package oop.db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import oop.data.BaseQuestion;
import oop.data.SimpleCategory;
import oop.data.SimpleUser;
import oop.db.Database;

public class BaseQuestionDAO {

	public static List<BaseQuestion> fetchByCategory(int categoryId, int start,
			int length) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT ques_id, ques_content, ques_level, cat_id, "
					+ "cat_name, ques_author, user_name "
					+ "FROM  tblQuestion, tblCategory, tblUser "
					+ "WHERE ques_category = cat_id AND ques_author = user_id "
					+ "AND cat_id = ? " + "LIMIT ?, ?";
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, categoryId);
			stmt.setInt(2, start);
			stmt.setInt(3, length);
			rs = stmt.executeQuery();
			return BaseQuestionDAO.fromResultSet(rs);
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				conn.close();
		}
	}

	public static List<BaseQuestion> fetchByAuthor(int authorId, int start,
			int length) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT ques_id, ques_content, ques_level, cat_id, "
					+ "cat_name, ques_author, user_name "
					+ "FROM  tblQuestion, tblCategory, tblUser "
					+ "WHERE ques_category = cat_id AND ques_author = user_id "
					+ "AND user_id = ? " + "LIMIT ?, ?";
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, authorId);
			stmt.setInt(2, start);
			stmt.setInt(3, length);
			rs = stmt.executeQuery();
			return fromResultSet(rs);
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				conn.close();
		}
	}

	public static List<BaseQuestion> fetch(int start, int length)
			throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT ques_id, ques_content, ques_level, cat_id, "
					+ "cat_name, ques_author, user_name "
					+ "FROM  tblQuestion, tblCategory, tblUser "
					+ "WHERE ques_category = cat_id AND ques_author = user_id "
					+ "LIMIT ?, ?";
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, start);
			stmt.setInt(2, length);
			rs = stmt.executeQuery();
			return fromResultSet(rs);
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				conn.close();
		}
	}

	public static List<BaseQuestion> fetchById(int id) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT ques_id, ques_content, ques_level, cat_id, "
					+ "cat_name, ques_author, user_name "
					+ "FROM  tblQuestion, tblCategory, tblUser "
					+ "WHERE ques_category = cat_id AND ques_author = user_id "
					+ "AND ques_id = " + id;
	
			conn = Database.get().getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			return fromResultSet(rs);
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				conn.close();
		}
	}

	public static List<BaseQuestion> fromResultSet(ResultSet rs)
			throws SQLException {
		List<BaseQuestion> questions = new ArrayList<BaseQuestion>();
		while (rs.next()) {
			questions.add(new BaseQuestion(rs.getInt(1), rs.getString(2), rs
					.getInt(3), new SimpleCategory(rs.getInt(4), rs
					.getString(5)), new SimpleUser(rs.getInt(6), rs
					.getString(7))));
		}
		return questions;
	}

	public static int count() throws SQLException {
		String sql = "SELECT COUNT(*) FROM  tblQuestion";
		return  Database.get().count(sql);
	}

	public static int countByCategory(int categoryId) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT COUNT(*) " + "FROM  tblQuestion "
					+ "WHERE ques_category = ?";
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, categoryId);
			rs = stmt.executeQuery();
			rs.next();
			return rs.getInt(1);
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				conn.close();
		}
	}

	public static int countByAuthor(int authorId) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT COUNT(*) " + "FROM  tblQuestion "
					+ "WHERE ques_author = ?";
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, authorId);
			rs = stmt.executeQuery();
			rs.next();
			return rs.getInt(1);
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				conn.close();
		}
	}

	public static BaseQuestion create(String content, int level,
			SimpleCategory category, SimpleUser author) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "INSERT INTO tblQuestion (" +
					"ques_content, ques_level, ques_category, ques_author) " +
					"VALUES (?, ?, ?, ?)";
			
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, content);
			stmt.setInt(2, level);
			stmt.setInt(3, category.getId());
			stmt.setInt(4, author.getId());
			stmt.executeUpdate();
			
			rs = stmt.getGeneratedKeys();
			rs.next();
			int id = rs.getInt(1);
			
			return new BaseQuestion(id, content, level, category, author);
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}
	
	public static void persist(BaseQuestion q) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "UPDATE tblQuestion SET " +
					"ques_content=?, ques_level=?, ques_category=?, ques_author=? " +
					"WHERE ques_id = ?";
			
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, q.getContent());
			stmt.setInt(2, q.getLevel());
			stmt.setInt(3, q.getCategory().getId());
			stmt.setInt(4, q.getAuthor().getId());
			stmt.setLong(5, q.getId());
			stmt.executeUpdate();
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

}
